﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Project.dalSql
{
    public class dalSqlBaseCommandHelper
    {
        public static string ConnectionString
        {
            get
            {
                if (System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectionString"] == null)
                {
                    throw new Exception("app.config 中ConnectionStrings节点不存在名字为SqlConnectionString的连接");
                }
                return System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectionString"].ToString().Trim();
            }
        }

        public static bool IsCanConnection(string connectionString)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static Int32 ExecuteScalar(SqlCommand Command)
        {
            if (String.IsNullOrEmpty(dalSqlBaseCommandHelper.ConnectionString))
            {
                throw new Exception("数据库连接字符串不存在");
            }
            using (SqlConnection Connection = new SqlConnection(dalSqlBaseCommandHelper.ConnectionString))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                return ExecuteScalar(Connection, Command);
            }
        }

        private static Int32 ExecuteScalar(SqlConnection Connection, SqlCommand Command)
        {
            if (Connection == null)
            {
                throw new Exception("数据库连接SqlConnection对象不存在");
            }
            Command.CommandTimeout = 600;
            PrepareCommand(Command, Connection, (SqlTransaction)null);
            int retval = -1;
            try
            {
                retval = Convert.ToInt32(Command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            Command.Parameters.Clear();
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            return retval;
        }

        public static Int32 ExecuteScalar(SqlTransaction Transaction, SqlCommand Command)
        {
            if (Transaction == null)
            {
                throw new Exception("事务Transaction对象不存在");
            }
            if (Transaction != null && Transaction.Connection == null)
            {
                throw new Exception("数据库连接字符串不存在");
            }
            Command.CommandTimeout = 600;
            PrepareCommand(Command, Transaction.Connection, Transaction);
            int retval = -1;
            try
            {
                retval = Convert.ToInt32(Command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            Command.Parameters.Clear();
            return retval;
        }

        public static Int32 ExecuteNonQuery(SqlCommand Command)
        {
            if (String.IsNullOrEmpty(dalSqlBaseCommandHelper.ConnectionString))
            {
                throw new Exception("数据库连接字符串不存在");
            }
            using (SqlConnection Connection = new SqlConnection(dalSqlBaseCommandHelper.ConnectionString))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                return ExecuteNonQuery(Connection, Command);
            }
        }

        private static Int32 ExecuteNonQuery(SqlConnection Connection, SqlCommand Command)
        {
            if (Connection == null)
            {
                throw new Exception("数据库连接SqlConnection对象不存在");
            }
            Command.CommandTimeout = 600;
            PrepareCommand(Command, Connection, (SqlTransaction)null);
            int retval = -1;
            try
            {
                retval = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            Command.Parameters.Clear();
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            return retval;
        }

        public static Int32 ExecuteNonQuery(SqlTransaction Transaction, SqlCommand Command)
        {
            if (Transaction == null)
            {
                throw new Exception("事务Transaction对象不存在");
            }
            if (Transaction != null && Transaction.Connection == null)
            {
                throw new Exception("数据库连接字符串不存在");
            }
            Command.CommandTimeout = 600;
            PrepareCommand(Command, Transaction.Connection, Transaction);
            int retval = -1;
            try
            {
                retval = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                throw ex;
            }
            Command.Parameters.Clear();
            return retval;
        }

        public static Int32 ExecuteNonQuery(SqlBaseCommand BaseCommand)
        {
            if (String.IsNullOrEmpty(dalSqlBaseCommandHelper.ConnectionString))
            {
                throw new Exception("数据库连接字符串不存在");
            }
            using (SqlConnection Connection = new SqlConnection(dalSqlBaseCommandHelper.ConnectionString))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                return ExecuteNonQuery(Connection, BaseCommand);
            }
        }

        private static Int32 ExecuteNonQuery(SqlConnection Connection, SqlBaseCommand BaseCommand)
        {
            if (Connection == null)
            {
                throw new Exception("数据库连接SqlConnection对象不存在");
            }
            BaseCommand.Command.CommandTimeout = 600;
            PrepareCommand(BaseCommand.Command, Connection, (SqlTransaction)null);
            int retval = -1;
            try
            {
                retval = BaseCommand.Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            BaseCommand.Command.Parameters.Clear();
            if (Connection.State != ConnectionState.Closed)
            {
                Connection.Close();
            }
            return retval;
        }

        public static Int32 ExecuteNonQuery(SqlTransaction Transaction, SqlBaseCommand BaseCommand)
        {
            if (Transaction == null)
            {
                throw new Exception("事务Transaction对象不存在");
            }
            if (Transaction != null && Transaction.Connection == null)
            {
                throw new Exception("数据库连接字符串不存在");
            }
            BaseCommand.Command.CommandTimeout = 600;
            PrepareCommand(BaseCommand.Command, Transaction.Connection, Transaction);
            int retval = -1;
            try
            {
                retval = BaseCommand.Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                throw ex;
            }
            BaseCommand.Command.Parameters.Clear();
            return retval;
        }

        public static DataSet ExecuteDataset(SqlCommand Command)
        {
            if (String.IsNullOrEmpty(dalSqlBaseCommandHelper.ConnectionString))
            {
                throw new Exception("数据库连接字符串不存在");
            }
            using (SqlConnection Connection = new SqlConnection(dalSqlBaseCommandHelper.ConnectionString))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                return ExecuteDataset(Connection, Command);
            }
        }

        private static DataSet ExecuteDataset(SqlConnection Connection, SqlCommand Command)
        {
            if (Connection == null)
            {
                throw new Exception("数据库连接SqlConnection对象不存在");
            }
            Command.CommandTimeout = 600;
            PrepareCommand(Command, Connection, (SqlTransaction)null);
            using (SqlDataAdapter da = new SqlDataAdapter(Command))
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                Command.Parameters.Clear();
                if (Connection.State != ConnectionState.Closed)
                {
                    Connection.Close();
                }
                return ds;
            }
        }

        public static DataSet ExecuteDataset(SqlTransaction Transaction, SqlCommand Command)
        {
            if (Transaction == null)
            {
                throw new Exception("事务Transaction对象不存在");
            }
            if (Transaction != null && Transaction.Connection == null)
            {
                throw new Exception("数据库连接字符串不存在");
            }
            Command.CommandTimeout = 600;
            PrepareCommand(Command, Transaction.Connection, Transaction);
            using (SqlDataAdapter da = new SqlDataAdapter(Command))
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                Command.Parameters.Clear();
                return ds;
            }
        }

        public static DataTable ExecuteDataTable(SqlCommand Command)
        {
            return ExecuteDataset(Command).Tables[0];
        }

        public static DataTable ExecuteDataTable(SqlTransaction Transaction, SqlCommand Command)
        {
            return ExecuteDataset(Transaction, Command).Tables[0];
        }

        public static DataTable ExecuteDataTable(SqlConnection Connection, SqlCommand Command)
        {
            return ExecuteDataset(Connection, Command).Tables[0];
        }

        private static void PrepareCommand(SqlCommand Command, SqlConnection Connection, SqlTransaction Transaction)
        {
            if (Command == null)
            {
                throw new Exception("数据库命令command对象不存在");
            }
            Command.Connection = Connection;
            if (Transaction != null)
            {
                if (Transaction.Connection == null)
                {
                    throw new Exception("数据库连接字符串不存在");
                }
                Command.Transaction = Transaction;
            }
        }

        public static Boolean ExecuteNonQuery(List<SqlBaseCommand> BaseCommandList)
        {
            if (BaseCommandList == null)
            {
                throw new Exception("SqlBaseCommand不存在");
            }
            if (BaseCommandList.Count == 0)
            {
                throw new Exception("SqlBaseCommand只有O条");
            }
            if (BaseCommandList.Count == 1)
            {
                return BaseCommandList[0].Comparison(ExecuteNonQuery(BaseCommandList[0]));
            }
            else
            {
                dalSqlTransaction Transaction = new dalSqlTransaction();
                Transaction.BeginTransaction();
                for (int i = 0; i < BaseCommandList.Count; i++)
                {
                    if (!BaseCommandList[i].Comparison(ExecuteNonQuery(Transaction.Transaction, BaseCommandList[i])))
                    {
                        Transaction.Rollback();
                        return false;
                    }
                }
                Transaction.Commit();
                return true;
            }
        }


    }
}
